package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.GUID;
import edu.byu.intex2.data.bo.Manufacturer;
import edu.byu.intex2.data.bo.Order;
import edu.byu.intex2.data.bo.OrderLine;
import edu.byu.intex2.data.bo.PartsAtServiceFacility;
import edu.byu.intex2.data.bo.ServiceFacility;
import edu.byu.intex2.javamail.SendMail;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Order Data Access Object
 * @author Brady
 */
public class OrderDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static OrderDAO instance = null;

    /** Creates a new instance of OrderDAO */
    private OrderDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized OrderDAO getInstance() {
        if (instance == null) {
            instance = new OrderDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new order in the database */
    public Order create(String id) throws DataException {
        Order order = new Order(id);
        order.setObjectAlreadyInDB(false);
        Cache.getInstance().put(order.getId(), order);
        return order;
    }//create

    ////////////////////////////////////////////
    ///   READ methods
    /**
     * Reads an existing order from the database     */
    public Order read(String id) throws DataException {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (Order) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try {
            return read(id, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the order business object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing order from the database
     * @return order
     */
    synchronized Order read(String id, Connection conn) throws Exception {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (Order) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM part_order where id=?");

        try {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next()) {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("Order with id '" + id + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Internal method to create a order object from a record  */
    synchronized Order readRecord(ResultSet rs, Connection conn) throws Exception {
        if (Cache.getInstance().containsKey(rs.getString("id"))) {
            return (Order) Cache.getInstance().get(rs.getString("id"));
        }
        // Create new order object
        Order order = new Order(rs.getString("id"));

        // Order is already in DB
        order.setObjectAlreadyInDB(true);

        // Put order in cache
        Cache.getInstance().put(order.getId(), order);

        // Set Order Attributes
//        order.setTotalPrice(rs.getDouble("total_price"));
//        order.setTax(rs.getDouble("tax"));
        order.setDate(rs.getDate("order_date"));
        order.setServiceFacilityId(rs.getString("service_facility_id"));
        order.setManufacturerId(rs.getString("manufacturer_id"));

        // Order is dirty and needs to be saved to DB
        order.setDirty(false);

        return order;
    }//readRecord


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing order in the database */
    public void save(Order order) throws DataException {
        // get a connection from the pool, save all of the orders first using the other order method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try {
            save(order, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e) {
            try {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the order database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the order object information.", e);
        } finally {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a order in the database */
    void save(Order order, Connection conn) throws Exception {
        // put the order object to cache
        Cache.getInstance().put(order.getId(), order);

        // decide whether to update or insert into the database
        if (order.isObjectAlreadyInDB() && order.isDirty()) {
            update(order, conn);
        } else if (order.isDirty()) {
            insert(order, conn);
        }//if

        // Save order lines
        List<OrderLine> orderLines = order.getOrderLines();
        for (OrderLine ol : orderLines) {
            OrderLineDAO.getInstance().saveNoCommit(ol, conn);
        }
    }

    /** External method to update or create a order in the database without committing connection */
    public void saveNoCommit(Order order, Connection conn, Boolean alreadyInDB) throws Exception {
        // decide whether to update or insert into the database
        if (alreadyInDB) {
            update(order, conn);
        } else {
            insert(order, conn);
        }
    }

    /** Saves an existing order to the database */
    private void update(Order order, Connection conn) throws Exception {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE part_order SET order_date=?, manufacturer_id=?, " +
                "service_facility_id=? WHERE id=?");
        try {
            // Get Order Attributes and add them to the statement
            stmt.setDate(1, new java.sql.Date(order.getDate().getTime()));
            stmt.setString(2, order.getManufacturerId());
            stmt.setString(3, order.getServiceFacilityId());
            stmt.setString(4, order.getId());
            stmt.execute();

            // Order is clean!
            order.setDirty(false);
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new order into the database */
    private void insert(Order order, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO part_order (id, order_date, manufacturer_id, service_facility_id) VALUES (?, ?, ?, ?)");
        try {
            // Set Order Attributes
            stmt.setString(1, order.getId());
            stmt.setDate(2, new java.sql.Date(order.getDate().getTime()));
            stmt.setString(3, order.getManufacturerId());
            stmt.setString(4, order.getServiceFacilityId());
            stmt.execute();

            order.setObjectAlreadyInDB(true); // now in DB
            order.setDirty(false); // object is clean
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(Order order) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods
    ////////////////////////////////////////////////
    ///   BATCH methods
    /** Generate our daily orders via Batch Processing */
    public void generateDailyOrders() throws Exception {
        // Get Reorder Parts
        List<PartsAtServiceFacility> psfs = PartsAtServiceFacilityDAO.getInstance().getReorderPSFs();

        // Orders List
        List<Order> orders = new ArrayList<Order>();

        // Iterate through each psf
        for (PartsAtServiceFacility psf : psfs) {
            // Initialize the order for the PSF
            Order psfOrder = new Order(GUID.generate());

            // Get manufacturer by part 
            Manufacturer m = psf.getPart().getManufacturer();

            // Get Service Facility
            ServiceFacility sf = psf.getServiceFacility();

            // Boolean to see if order with service facility and manufacturer exists
            Boolean sfAndManAlreadyInAnOrder = false;

            // Loop through each order
            for (Order order : orders) {
                if (order.getServiceFacilityId().equals(sf.getId()) && order.getManufacturerId().equals(m.getId())) {
                    psfOrder = order;
                    sfAndManAlreadyInAnOrder = true;
                    break;
                }
            }

            // If sfAndManAlreadyInAnOrder is false, we need to Create a new Order
            if (sfAndManAlreadyInAnOrder == false) {
                try {
                    psfOrder = OrderDAO.getInstance().create(psfOrder.getId());
                    psfOrder.setManufacturer(m);
                    psfOrder.setServiceFacility(sf);
                    psfOrder.setDate(new java.util.Date());
                    orders.add(psfOrder); // add the psfOrder to our list of Orders
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

            try {
                // Create our OrderLine
                OrderLine ol = OrderLineDAO.getInstance().create(psfOrder, psf.getPart());
                ol.setQuantity(psf.getRepurchaseQuantity());

                // Add OrderLine to psfOrder
                psfOrder.addOrderLine(ol);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }// for psfs

        // Loop through all our orders and prepare the email and save the order.
        for (Order order : orders) {
            // Prepare Email
            prepareEmail(order);

            // Save the order
            try {
                order.save();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /** Prepare email of order */
    private void prepareEmail(Order order) {
        ////////////////////////////////////
        // Email Header Information
        String from = "ordering@madisonautogroup.info";
        String[] to = {order.getManufacturer().getEmail(), "satara@gmail.com"};
        String subject = order.getManufacturer().getName() + " - New Order from Madison Automative Group";
        
        ////////////////////////////////////
        // Compose Message of our order
        String message = "";

        // Order Headers
        message += "Madison Automotive Group 1-5 \n";
        message += "Service Facility Address: ";
        message += order.getServiceFacility().getAddress() + "\n";
        message += "-----------------------------------------------------\n\n";

        // Display all orderline details
        for (OrderLine ol : order.getOrderLines()){
            message += "Part Number: " + ol.getPart().getPartNumber() + "\n";
            message += "Part Name: " + ol.getPart().getName() + "\n";
            message += "Quantity: " + ol.getQuantity() + "\n\n";
        }
        message += "-----------------------------------------------------\n\n";
        message += "Please invoice and send parts immediately.\n\n";
        message += "Sincerely,\n";
        message += "- Brady White\n";


        SendMail sendMail = new SendMail(from, to, subject, message);
        sendMail.send();
    }
}
